<!DOCTYPE html>
<html>
  <head>
  <title>
      
          oracle 记录 - jun&#39;s home
      
  </title>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <meta name="author" content="map[]" />
  <link rel="shortcut icon" type="image/x-icon" href="https://dengxiujun.gitee.io/img/favicon.ico">

  
  
  
  
  
  <link rel="stylesheet" href="https://dengxiujun.gitee.io/style.min.7135f8265f0e5668aaf68b968cf560537cb8017044dfb3bdcd7a5d0d8a806b3d.css">
  <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.8.1/css/all.css" integrity="sha384-50oBUHEmvpQ+1lW4y57PTFmhCaXp0ML5d60M1M7uH2+nqUivzIebhndOJK28anvf" crossorigin="anonymous">

  
  

  <meta property="og:title" content="oracle 记录" />
<meta property="og:description" content="01. dmp文件导入步骤 01) 用管理员登录: # 方法一 system as sysdba # 方法二 sys as sysdba 02) 创建用户 CREATE USER &lt;USERNAME&gt; IDENTIFIED BY &lt;PASSWORD&gt;; 03) 赋权限给新用户 GRANT CREATE USER, DROP USER, ALTER USER, CREATE ANY VIEW, DROP ANY VIEW, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DBA, CONNECT, RESOURCE, CREATE" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://dengxiujun.gitee.io/blog/oracle_reademe/" />
<meta property="article:published_time" content="2019-04-01T10:55:56&#43;08:00"/>
<meta property="article:modified_time" content="2019-04-01T10:55:56&#43;08:00"/>

<meta itemprop="name" content="oracle 记录">
<meta itemprop="description" content="01. dmp文件导入步骤 01) 用管理员登录: # 方法一 system as sysdba # 方法二 sys as sysdba 02) 创建用户 CREATE USER &lt;USERNAME&gt; IDENTIFIED BY &lt;PASSWORD&gt;; 03) 赋权限给新用户 GRANT CREATE USER, DROP USER, ALTER USER, CREATE ANY VIEW, DROP ANY VIEW, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DBA, CONNECT, RESOURCE, CREATE">


<meta itemprop="datePublished" content="2019-04-01T10:55:56&#43;08:00" />
<meta itemprop="dateModified" content="2019-04-01T10:55:56&#43;08:00" />
<meta itemprop="wordCount" content="1796">



<meta itemprop="keywords" content="oracle,oracle 临时表空间,oracle表空间,oracle 导入导出,oracle 常用操作," />
<meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="oracle 记录"/>
<meta name="twitter:description" content="01. dmp文件导入步骤 01) 用管理员登录: # 方法一 system as sysdba # 方法二 sys as sysdba 02) 创建用户 CREATE USER &lt;USERNAME&gt; IDENTIFIED BY &lt;PASSWORD&gt;; 03) 赋权限给新用户 GRANT CREATE USER, DROP USER, ALTER USER, CREATE ANY VIEW, DROP ANY VIEW, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DBA, CONNECT, RESOURCE, CREATE"/>

  <!--[if lte IE 9]>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js"></script>
  <![endif]-->

  <!--[if lt IE 9]>
  <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
  <![endif]-->

  </head>

  <body>
    
  <h1>oracle 记录</h1>
  <header>
  
  <div class="avatar">
    <img class="avatarMask" src="https://dengxiujun.gitee.io/img/avatar.jpg">
    <a href="https://dengxiujun.gitee.io"><img src="https://dengxiujun.gitee.io/img/avatar-border.svg" }}></a>
  </div>
  
  <h2><a class="author" href="https://dengxiujun.gitee.io" style="text-decoration:none;"></a></h2>
</header>

  
  <p class="date">April 1, 2019</p>
  
  <div id="contentBody">
    

<h3 id="01-dmp文件导入步骤">01. dmp文件导入步骤</h3>

<p>01) 用管理员登录:</p>

<pre><code># 方法一
system as sysdba
# 方法二
sys as sysdba
</code></pre>

<p>02) 创建用户</p>

<pre><code>CREATE USER &lt;USERNAME&gt; IDENTIFIED BY &lt;PASSWORD&gt;;
</code></pre>

<p>03) 赋权限给新用户</p>

<pre><code>GRANT CREATE USER, DROP USER, ALTER USER, CREATE ANY VIEW, DROP ANY VIEW, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DBA, CONNECT, RESOURCE, CREATE SESSION TO &lt;用户名&gt;;
</code></pre>

<p>04) 打开cmd窗口</p>

<pre><code>IMP &lt;USERNAME&gt;/&lt;PASSWORD&gt;@&lt;SID&gt; FILE=D:\\XXX.DMP FULL=Y;
</code></pre>

<p>05) 导出命令:</p>

<pre><code># 导出某些指定的表:
exp &lt;username&gt;/&lt;password&gt;@&lt;SID&gt; file=d:\\xxx.dmp tables=(table1,table2,table3)
# 导出某个用户的表:
exp &lt;username&gt;/&lt;password&gt;@&lt;SID&gt; file=d:\\xxx.dmp owner=&lt;username&gt;
</code></pre>

<h3 id="02-常用操作">02. 常用操作</h3>

<p>01) 切换用户</p>

<pre><code># 方式一
CONNECT SYS/123456 AS SYSDBA;
# 方式二
CONN SCOTT/TIGER;
</code></pre>

<p>02) 退出sqlplus:</p>

<pre><code>EXIT;
</code></pre>

<p>03) 免登录进入 sqlplus:</p>

<pre><code># 方式一
sqlplus /nolog
# 方式二
sqlplus / as sysdba
</code></pre>

<p>04) 查询表信息:</p>

<pre><code>DESC &lt;TABLENAME&gt;;
</code></pre>

<p>05) 表结构修改字段:</p>

<pre><code>ALTER TABLE &lt;TABLE_NAME&gt; MODIFY (&lt;FIELD_NAME&gt; &lt;FIELD_TYPE&gt;);
</code></pre>

<p>06) 表结构增加字段:</p>

<pre><code>ALTER TABLE &lt;TABLE_NAME&gt; ADD &lt;FIELD_NAME&gt; &lt;FIELD_TYPE&gt;;
</code></pre>

<p>07) 表结构增加注释:</p>

<pre><code>COMMENT ON COLUMN &lt;TABLE_NAME&gt;.&lt;FIELD_NAME&gt; IS '&lt;COMMENT_CONTENT&gt;';
</code></pre>

<p>08) 显示当前用户:</p>

<pre><code>SHOW USER;
</code></pre>

<p>09) 解锁用户:</p>

<pre><code>ALTER USER &lt;USERNAME&gt; ACCOUNT UNLOCK;
</code></pre>

<p>10) 修改用户密码:</p>

<pre><code>ALTER USER &lt;USERNAME&gt; IDENTIFIED BY &lt;NEW_PASSWORD&gt;;
</code></pre>

<p>11) 显示当前数据库实例:</p>

<pre><code># 方式一
SHOW PARAMETER INTANCE;
# 方式二
SELECT INSTANCE_NAME FROM V$INSTANCE;
</code></pre>

<p>12) sql语句case when 的用法:</p>

<pre><code>case when &lt;condition&gt; then &lt;what to do when condition is true&gt; else &lt;what to do when condition is false&gt; end
</code></pre>

<p>13) 表结构字段名称修改:</p>

<pre><code>ALTER TABLE &lt;TABLE_NAME&gt; RENAME COLUMN &lt;OLD_FIELD_NAME&gt; TO &lt;NEW_FIELD_NAME&gt;;
</code></pre>

<h3 id="03-grant-赋于权限">03.GRANT 赋于权限</h3>

<p>常用的系统权限集合有以下三个:<br />
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)<br />
常用的数据对象权限有以下五个:<br />
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名, DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名</p>

<pre><code>GRANT CONNECT, RESOURCE TO &lt;TABLE_NAME&gt;;
GRANT SELECT ON &lt;TABLE_NAME&gt; TO &lt;USER_NAME&gt;;
GRANT SELECT, INSERT, DELETE ON &lt;TABLE_NAME&gt; TO &lt;USER_NAME&gt;, &lt;USER_NAME&gt;;
</code></pre>

<h3 id="04-revoke-回收权限">04.REVOKE 回收权限</h3>

<pre><code>REVOKE CONNECT, RESOURCE FROM &lt;USER_NAME&gt;;
REVOKE SELECT ON &lt;TABLE_NAME&gt; FROM &lt;USER_NAME&gt;;
REVOKE SELECT, INSERT, DELETE ON &lt;TABLE_NAME&gt; FROM &lt;USER_NAME&gt;, &lt;USER_NAME&gt;;
</code></pre>

<h3 id="05-重建并修改默认临时表空间办法">05. 重建并修改默认临时表空间办法:</h3>

<p>01) 查询当前数据库默认临时表空间名和查询临时表空间数据文件</p>

<pre><code>select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
</code></pre>

<p>02) 创建新的临时表空间</p>

<pre><code>create temporary tablespace temp02 tempfile 'E:\oracle\oradata\lims\TEMP02.DBF' size 1024M autoextend on;
</code></pre>

<p>03) 修改默认表空间为刚刚建立的临时表空间</p>

<pre><code>alter database default temporary tablespace temp02;
</code></pre>

<p>04) 查看用户所用临时表空间的情况</p>

<pre><code>SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;
</code></pre>

<p>05) 删除原来的临时表空间</p>

<pre><code>drop tablespace temp including contents and datafiles;
</code></pre>

<p>06) 查看所有表空间名确认临时表空间是否已删除</p>

<pre><code>select tablespace_name from dba_tablespaces;
</code></pre>

<h3 id="06-临时表空间相关处理">06. 临时表空间相关处理</h3>

<p>01) 查询默认临时表空间</p>

<pre><code>select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
</code></pre>

<p>02) 查询临时表空间数据文件状态:</p>

<pre><code>select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
</code></pre>

<p>03) 创建新的临时表空间</p>

<pre><code>create temporary tablespace temp_01 tempfile '/u01/app/oracle/oradata/XE/temp_01.dbf' size 1024M autoextend on;
</code></pre>

<p>04) 修改默认表空间为新的表空间</p>

<pre><code>alter database default temporary tablespace temp_01;
</code></pre>

<p>05) 修改临时表空间大小</p>

<pre><code>alter database tempfile '/u01/app/oracle/oradata/XE/temp.dbf' resize 512m;
</code></pre>

<p>06) 设置临时表空间自动增长</p>

<pre><code>alter database tempfile '/u01/app/oracle/oradata/XE/temp.dbf' autoextend on next 8m maxsize unlimited;
</code></pre>

<p>07) 查看临时表空间使用情况:</p>

<pre><code>SELECT tbs 表空间名, SUM(totalM) 总共大小M, SUM(usedM) 已使用空间M, SUM(remainedM) 剩余空间M, SUM(usedM) / SUM(totalM) * 100 已使用百分比, SUM(remainedM) / SUM(totalM) * 100 剩余百分比
FROM (
  SELECT tb.file_id ID, tb.tablespace_name tbs, tb.file_name NAME, tb.bytes / 1024 / 1024 totalM, (tb.bytes - SUM(NVL(ta.free_space, 0))) / 1024 / 1024 usedM,
    SUM(NVL(ta.free_space, 0) / 1024 / 1024) remainedM, SUM(NVL(ta.free_space, 0) /(tb.bytes) * 100), (100 - (SUM(NVL(ta.free_space, 0)) /(tb.bytes) * 100))
  FROM dba_temp_free_space ta, dba_temp_files tb
  WHERE ta.tablespace_name = tb.tablespace_name
  GROUP BY tb.tablespace_name, tb.file_name, tb.file_id, tb.bytes
  ORDER BY tb.tablespace_name
) GROUP BY tbs;
</code></pre>

<h3 id="07-表空间相关处理">07. 表空间相关处理</h3>

<p>01) 扩展表空间</p>

<pre><code>alter database datafile '/u01/app/oracle/oradata/XE/system.dbf' resize 1024m;
</code></pre>

<p>02) 表空间自动增长</p>

<pre><code>alter system datafile '/u01/app/oracle/oradata/XE/system_01.dbf' autoextend on next 50m maxsize 500m;
</code></pre>

<p>03) 新增表空间数据文件</p>

<pre><code>alter tablespace system add datafile '/u01/app/oracle/oradata/XE/system_01.dbf' size 1024m;
</code></pre>

<p>04) 查询表空间的所有数据文件</p>

<pre><code>select * from dba_data_files where tablespace_name='SYSTEM';
</code></pre>

<p>05) 查询表属于哪个表空间</p>

<pre><code>select tablespace_name,table_name from user_talbes where table_name='employ';
</code></pre>

<p>06) 查看表空间使用情况</p>

<pre><code>SELECT tbs 表空间名, SUM(totalM) 总共大小M, SUM(usedM) 已使用空间M, SUM(remainedM) 剩余空间M, SUM(usedM) / SUM(totalM) * 100 已使用百分比, SUM(remainedM) / SUM(totalM) * 100 剩余百分比
FROM (
  SELECT tb.file_id ID, tb.tablespace_name tbs, tb.file_name NAME, tb.bytes / 1024 / 1024 totalM, (tb.bytes - SUM(NVL(ta.bytes, 0))) / 1024 / 1024 usedM,
    SUM(NVL(ta.bytes, 0) / 1024 / 1024) remainedM, SUM(NVL(ta.bytes, 0) /(tb.bytes) * 100), (100 - (SUM(NVL(ta.bytes, 0)) /(tb.bytes) * 100))
  FROM dba_free_space ta, dba_data_files tb
  WHERE ta.file_id = tb.file_id
  GROUP BY tb.tablespace_name, tb.file_name, tb.file_id, tb.bytes
  ORDER BY tb.tablespace_name
) GROUP BY tbs;
</code></pre>

<h3 id="00-其它备忘">00. 其它备忘</h3>

<p>01) sql语句和sql命令虽然大小写都可以, 但是建议大写, 大写执行速度快, 同时有些命令只能大写!<br />
02) sql语句和sql命令必须用分号结尾, cmd中的命令不用分号!<br />
03) sqlplus 登录密码区分大小写
04) 查询 DIRECTORY 操作需要的权限</p>

<pre><code>SELECT DISTINCT PRIVILEGE FROM DBA_SYS_PRIVS WHERE PRIVILEGE LIKE '%DIRECTORY%';
</code></pre>

<p>05) 查询当前设定的目录配置</p>

<pre><code>SELECT * FROM DBA_DIRECTORIES;
</code></pre>

<p>06) 赋予SCOTT用户删除和创建目录配置的权限</p>

<pre><code>GRANT DROP ANY DIRECTORY, CREATE ANY DIRECTORY TO SCOTT;
</code></pre>

<p>07) 回收SCOTT用户删除和创建目录配置的权限</p>

<pre><code>REVOKE DROP ANY DIRECTORY, CREATE ANY DIRECTORY FROM SCOTT;
</code></pre>

<p>08) 创建某个目录配置</p>

<pre><code>CREATE OR REPLACE DIRECTORY &lt;dir_name&gt; AS &lt;dir_path&gt;;
</code></pre>

<p>09) 删除某个目录配置</p>

<pre><code>DROP DIRECTORY &lt;dir_name&gt;;
</code></pre>

<p>10) 赋予和回收某个目录的读写权限:</p>

<pre><code>GRANT READ ON DIRECTORY &lt;dir_name&gt; TO SCOTT;
GRANT WRITE ON DIRECTORY &lt;dir_name&gt; TO SCOTT;
REVOKE READ ON DIRECTORY &lt;dir_name&gt; FROM SCOTT;
REVOKE WRITE ON DIRECTORY &lt;dir_name&gt; FROM SCOTT;
</code></pre>

<p>11) 查询当前schma下的所有表: SELECT T.* FROM USER_TABLES T;<br />
12) 查出所有表和对应schma以及表的字段</p>

<pre><code>SELECT DISTINCT DCC.OWNER AS &quot;用户&quot;, DCC.TABLE_NAME AS &quot;表名&quot;, DCC.COLUMN_NAME AS &quot;字段名&quot;, DTC.DATA_TYPE AS &quot;数据类型&quot;, DCC.COMMENTS AS &quot;字段描述&quot;,
       (CASE WHEN CONSTS.COLUMN_NAME IS NOT NULL THEN '是' ELSE '' END) AS &quot;是否主键&quot;
FROM DBA_TAB_COLS DTC
LEFT JOIN DBA_COL_COMMENTS DCC ON (DTC.COLUMN_NAME = DCC.COLUMN_NAME AND DTC.TABLE_NAME = DCC.TABLE_NAME AND DTC.OWNER = DCC.OWNER)
LEFT JOIN DBA_TAB_COMMENTS UTC ON (DTC. OWNER = UTC. OWNER AND DTC.TABLE_NAME = UTC.TABLE_NAME)
LEFT JOIN (SELECT CU.* FROM DBA_CONS_COLUMNS CU, DBA_CONSTRAINTS AU WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = 'P' ) CONSTS
  ON (DTC.OWNER = CONSTS.OWNER AND DTC.TABLE_NAME = CONSTS.TABLE_NAME AND DTC.COLUMN_NAME = CONSTS.COLUMN_NAME)
WHERE DTC.OWNER IN ('BOMMGMT', 'CFGMGMT', 'CHGMGMT', 'CORE', 'CUST', 'MSTDATA', 'INTEGRATION')
ORDER BY DCC.OWNER, DCC.TABLE_NAME;
</code></pre>

<p>13) 数据库死锁查询</p>

<pre><code>select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in  (select session_id from v$locked_object));
select distinct  'alter system kill session ''' ||sid||','||serial#||''''||';' as &quot;kill sql&quot; from V$locked_object t1 left join V$session t2 on t1.session_id = t2.sid;
</code></pre>

<p>14) 约束查询: SELECT UC.TABLE_NAME, UC.CONSTRAINT_NAME, UC.* FROM USER_CONSTRAINTS UC;<br />
15) 查询所有列名: SELECT * FROM ALL_TAB_COLS WHERE TABLE_NAME = &lsquo;XXX&rsquo;;<br />
16) 查找alert日志, 需要通过 sqlplus / as sysdba 登陆, 然后输入: show parameter dump;</p>

<h3 id="附件">附件</h3>

<p><a href="https://dengxiujun.gitee.io/files/oracle_readme/oracle_readme.txt" target="_blank">oracle记录txt版本</a></p>

  </div>
  <footer>
  <p>
  &copy; 2019 .
  Powered by <a href="https://gohugo.io/">Hugo</a>
  using the <a href="https://github.com/koirand/pulp/">pulp</a> theme.
  </p>
</footer>


  </body>
</html>
